package cn.edu.zafu.bigdata.x.zhouyuyan.work11;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class Database {
    private static final String URL = "jdbc:mysql://192.168.169.76:3306/learnjava";
    private static final String USER = "learn";
    private static final String PASSWORD = "learnpassword";

    private Connection conn;

    public Database() throws SQLException {
        conn = DriverManager.getConnection(URL, USER, PASSWORD);
}

    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");

            conn = DriverManager.getConnection(URL, USER, PASSWORD);
            stmt = conn.createStatement();

            String sqlCreateTable = "CREATE TABLE IF NOT EXISTS Students ("
                    + "id INT AUTO_INCREMENT PRIMARY KEY, "
                    + "name VARCHAR(255) NOT NULL, "
                    + "age INT NOT NULL)";
            stmt.executeUpdate(sqlCreateTable);

            // 可以添加更多的初始化操作，如插入初始数据等

            System.out.println("数据库初始化成功！");

        } catch (ClassNotFoundException e) {
            // 处理ClassNotFoundException异常
            //e.printStackTrace();
        } catch (SQLException e) {
            // 处理SQLException异常
            e.printStackTrace();
        } finally {
            // 5. 关闭资源
            try {
                if (stmt != null) stmt.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
            try {
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
    }

    public void addStudent(Student student) throws SQLException {
        String query = "INSERT INTO student_info (id, name, phone) VALUES (?, ?, ?)";
        try (PreparedStatement preparedStatement = conn.prepareStatement(query)) {
            preparedStatement.setString(1, student.getId());
            preparedStatement.setString(2, student.getName());
            preparedStatement.setString(3, student.getPhone());
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            if (e.getErrorCode() == 1062) { // Duplicate entry for primary key
                System.out.println("学号重复，请重新输入。");
            } else {
                throw e;
            }
        }
    }

    public Student getStudentById(String id) throws SQLException {
        String query = "SELECT * FROM student_info WHERE id = ?";
        try (PreparedStatement preparedStatement = conn.prepareStatement(query)) {
            preparedStatement.setString(1, id);
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                return new Student(resultSet.getString("id"), resultSet.getString("name"), resultSet.getString("phone"));
            }
        }
        return null;
    }

    public void updateStudent(Student student) throws SQLException {
        String query = "UPDATE student_info SET name = ?, phone = ? WHERE id = ?";
        try (PreparedStatement preparedStatement = conn.prepareStatement(query)) {
            preparedStatement.setString(1, student.getName());
            preparedStatement.setString(2, student.getPhone());
            preparedStatement.setString(3, student.getId());
            preparedStatement.executeUpdate();
        }
    }

    public void deleteStudent(String id) throws SQLException {
        String query = "DELETE FROM student_info WHERE id = ?";
        try (PreparedStatement preparedStatement = conn.prepareStatement(query)) {
            preparedStatement.setString(1, id);
            preparedStatement.executeUpdate();
        }
    }

    public List<Student> getAllStudents() throws SQLException {
        List<Student> students = new ArrayList<>();
        String query = "SELECT * FROM student_info";
        try (Statement statement = conn.createStatement();
             ResultSet resultSet = statement.executeQuery(query)) {
            while (resultSet.next()) {
                students.add(new Student(resultSet.getString("id"), resultSet.getString("name"), resultSet.getString("phone")));
            }
        }
        return students;
    }

    public void close() throws SQLException {
        if (conn != null && !conn.isClosed()) {
            conn.close();
        }
    }
}

